package hiring.tong.com.company.dao;





import java.util.ArrayList;
import java.util.List;






















import hiring.tong.com.common.DBUtil;
import hiring.tong.com.common.PageModel;
import hiring.tong.com.company.entity.ApplicationEntity;


public class ApplicationDao {

	public PageModel<ApplicationEntity> list(ApplicationEntity application, int pageNo, int defaultPageSize) {
		List<Object> paramList =new ArrayList<>();
		//String sql="select * from application where talent_id=? limit ?,?";
		String sql="select p.name as position$name,a.status ,a.id,c.name as company$name,a.app_time,a.handle_time from application a,position p,company c,resume r where a.position_id=p.id and a.talent_id=? and r.id=a.resume_id and p.company_id=c.id limit ?,?";
		paramList.add(application.getTalentId());
		paramList.add((pageNo - 1) * defaultPageSize);
		paramList.add(defaultPageSize);
		return DBUtil.pageexecuteQuery(ApplicationEntity.class, sql, pageNo, defaultPageSize, paramList);
//		Connection connection = null;
//		PreparedStatement preparedStatement = null;
//		ResultSet resultSet = null;
//		try {
//			connection = DBUtil.getConnection();
//			preparedStatement = connection.prepareStatement(sql);
//			DBUtil.setParam(preparedStatement,paramList);
//			resultSet = preparedStatement.executeQuery();
//			// 每批数据
//			List<ApplicationEntity> applicationList = new ArrayList<>();
//			while (resultSet.next()) {
//				// 第一条数据
//				ApplicationEntity applications = new ApplicationEntity();
//				applications.setId(resultSet.getInt("id"));
//				applications.setAppTime(resultSet.getTimestamp("app_time"));
//				applications.setHandleTime(resultSet.getTimestamp("handle_time"));
//				applications.setStatus(resultSet.getInt("status"));
//				applications.setPosition$name(resultSet.getString("position_name"));
//				applications.setCompany$name(resultSet.getString("company_name"));
//				// 把每一条数据放到一批数据里
//				applicationList.add(applications);
//			}
//			// 构建分页实体
//			return new PageModel<ApplicationEntity>(applicationList, pageNo,
//					defaultPageSize, getAllTalentVerifyRecords(application));
//		} catch (SQLException e) {
//		
//			throw new RuntimeException();
//		} finally {
//			DBUtil.close(connection, preparedStatement, resultSet);
//		}
//		
		
		
		
		
	}
			
//	private  int  getAllTalentVerifyRecords(ApplicationEntity application) {
//		// TODO Auto-generated method stub
//		List<Object> paramList =new ArrayList<>();
//		String sql="select count(*) from application where talent_id=?";
//		paramList.add(application.getTalentId());
//		Connection connection = null;
//		PreparedStatement preparedStatement = null;
//		ResultSet resultSet = null;
//		try {
//			connection = DBUtil.getConnection();
//			preparedStatement = connection.prepareStatement(sql);
//			DBUtil.setParam(preparedStatement,paramList);
//			resultSet = preparedStatement.executeQuery();
//			resultSet.next();
//			return resultSet.getInt(1);
//		} catch (SQLException e) {
//			
//			throw new RuntimeException();
//		} finally {
//			DBUtil.close(connection, preparedStatement, resultSet);
//		}
//		
//	}
	
	//检查用户是否已经投递了简历
	public ApplicationEntity unknowGet(int id, int talent_id) {
		// TODO Auto-generated method stub
		String sql="select * from application a where a.position_id=? and a.talent_id=?";
		return DBUtil.getexecuteQuery(ApplicationEntity.class, sql, id,talent_id);
	}
	public int add(int position_id, int talent_id, int resume_id) {
		// TODO Auto-generated method stub
		String sql="insert into application values(NULL,?,?,?,1,NOW(),NULL)";
		return DBUtil.executeUpdate(sql,position_id,talent_id,resume_id);
	}

	public PageModel<ApplicationEntity> ApplicationList(ApplicationEntity application, int pageNo,
			int defaultPageSize)  {
		// TODO Auto-generated method stub
		List<Object> paramList = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("p.name as 'position$name', ");
		sb.append("t.name as 'talent$name', ");
		sb.append("r.intention AS 'resume$intention', ");
		sb.append("a.id, ");
		sb.append("a.app_time, ");
		sb.append("a.`status`, ");
		sb.append("a.handle_time ");
		sb.append("from application a, resume r, position p, talent t ");
		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND a.talent_id = t.id ");
		if(application.getPosition$name()!=null&&"".equals(application.getPosition$name())==false){
			sb.append("AND p.name like  ? ");
			paramList.add("%"+application.getPosition$name()+"%");
		}
		if(application.getStatus()!=0){
			sb.append("AND a.status=? ");
			paramList.add(application.getStatus());
			
		}
		if(application.getPretime()!=null&&application.getNexttime()!=null&&!"".equals(application.getPretime())&&!"".equals(application.getNexttime())){
			
			
			sb.append("AND a.handle_time BETWEEN ?");	
			paramList.add(application.getPretime());
			sb.append(" AND ? ");
			paramList.add(application.getNexttime());
		}
		if(application.getPretime()!=null&&!"".equals(application.getPretime())&&"".equals(application.getNexttime())){
			sb.append("AND a.handle_time >= ?");
			paramList.add(application.getPretime());
		}
		if("".equals(application.getPretime())&&application.getNexttime()!=null&&!"".equals(application.getNexttime())){
			sb.append("AND a.handle_time <= ?");
			paramList.add(application.getNexttime());
		}
		sb.append("AND p.company_id = ? ");
		int companyId = application.getCompany$id();
		paramList.add(companyId);
		sb.append("limit ?, ? ");
		paramList.add((pageNo - 1) * defaultPageSize);
		paramList.add(defaultPageSize);
		System.out.println(sb.toString());
	
//		
//		
//		
//		} catch (ClassNotFoundException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
        return DBUtil.pageExecuteQuery(ApplicationEntity.class, sb.toString(), pageNo, defaultPageSize, paramList);
//		Connection connection = null;
//		PreparedStatement preparedStatement = null;
//		ResultSet resultSet = null;
//		try {
//			connection = DBUtil.getConnection();
//			preparedStatement = connection.prepareStatement(sb.toString());
//			DBUtil.setParam(preparedStatement,paramList);
//			resultSet = preparedStatement.executeQuery();
//			// 每批数据
//			List<ApplicationEntity> applicationList = new ArrayList<>();
//			while (resultSet.next()) {
//				// 第一条数据
//				ApplicationEntity applications = new ApplicationEntity();
//				applications.setPosition$name(resultSet.getString("position$name"));
//				applications.setTalent$name(resultSet.getString("talent$name"));
//				applications.setResume$intention(resultSet.getString("resume$intention"));
//				applications.setId(resultSet.getInt("id"));
//				applications.setAppTime(resultSet.getTimestamp("app_time"));
//				applications.setStatus(resultSet.getInt("status"));
//				applications.setHandleTime(resultSet.getTimestamp("handle_time"));
//				
//				// 把每一条数据放到一批数据里
//				applicationList.add(applications);
//			}
//	
//			// 构建分页实体
//			return new PageModel<ApplicationEntity>(applicationList, pageNo,
//					defaultPageSize, getAllCompanyVerifyRecords(application));
//		} catch (SQLException e) {
//			
//			throw new RuntimeException();
//		} finally {
//			DBUtil.close(connection, preparedStatement, resultSet);
//		}
	}



//	private int getAllCompanyVerifyRecords(ApplicationEntity application) {
//		// TODO Auto-generated method stub
//		List<Object> paramList = new ArrayList<>();
//		StringBuilder sb = new StringBuilder();
//		sb.append("SELECT ");
//		sb.append("count(*) ");
//		sb.append("FROM application a, resume r, position p, talent t ");
//		sb.append("WHERE a.resume_id = r.id AND a.position_id = p.id AND a.talent_id = t.id ");
//if(application.getPretime()!=null&&application.getNexttime()!=null&&!"".equals(application.getPretime())&&!"".equals(application.getNexttime())){
//			
//			
//			sb.append("AND a.handle_time BETWEEN ?");	
//			paramList.add(application.getPretime());
//			sb.append(" AND ? ");
//			paramList.add(application.getNexttime());
//		}
//		if(application.getPretime()!=null&&!"".equals(application.getPretime())&&"".equals(application.getNexttime())){
//			sb.append("AND a.handle_time >= ?");
//			paramList.add(application.getPretime());
//		}
//		if("".equals(application.getPretime())&&application.getNexttime()!=null&&!"".equals(application.getNexttime())){
//			sb.append("AND a.handle_time <= ?");
//			paramList.add(application.getNexttime());
//		}
//		sb.append("AND p.company_id = ? ");
//		int companyId = application.getCompany$id();
//		paramList.add(companyId);
//		Connection connection = null;
//		PreparedStatement preparedStatement = null;
//		ResultSet resultSet = null;
//		try {
//			connection = DBUtil.getConnection();
//			preparedStatement = connection.prepareStatement(sb.toString());
//			DBUtil.setParam(preparedStatement,paramList);
//			resultSet = preparedStatement.executeQuery();
//			resultSet.next();
//			return resultSet.getInt(1);
//		} catch (SQLException e) {
//			
//			throw new RuntimeException();
//		} finally {
//			DBUtil.close(connection, preparedStatement, resultSet);
//		}
//		
//	}
//
	public int verify(int applicationId, int status) {
		// TODO Auto-generated method stub
		String sql="update application set status=?,handle_time=Now() where id=?";
		return DBUtil.executeUpdate(sql, status,applicationId);
	}
}
